"""
有声读物Repository实现
"""

from typing import List, Optional, Dict, Any
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, and_, or_, func, desc, asc
from datetime import datetime, timedelta, timezone

from .base import BaseRepository, CacheableRepository
from .database import transactional
from ..models.audiobook import (
    AudioBook, AudioBookChapter, AudioBookProgress, 
    AudioBookReview, AudioBookTag, AudioBookStatus
)
from ..models.user import User
from ..core.exceptions import NotFoundError, ValidationError
from ..core.logging import get_logger

logger = get_logger(__name__)


class AudioBookRepository(BaseRepository[AudioBook, Any, Any]):
    """有声读物Repository"""
    
    def __init__(self, session: AsyncSession = None):
        super().__init__(AudioBook, session)
    
    @transactional
    async def create_with_chapters(
        self,
        audiobook_data: Dict[str, Any],
        chapters_data: List[Dict[str, Any]]
    ) -> AudioBook:
        """创建有声读物和章节"""
        try:
            # 创建有声读物
            audiobook = AudioBook(**audiobook_data)
            db_audiobook = await self.db.insert(audiobook)
            
            # 创建章节
            chapters = []
            for chapter_data in chapters_data:
                chapter_data['audiobook_id'] = db_audiobook.id
                chapter = AudioBookChapter(**chapter_data)
                db_chapter = await self.db.insert(chapter)
                chapters.append(db_chapter)
            
            # 更新章节数量
            db_audiobook.chapters_count = len(chapters)
            await self.db.update(db_audiobook)
            
            logger.info(f"Created audiobook {db_audiobook.id} with {len(chapters)} chapters")
            return db_audiobook
            
        except Exception as e:
            logger.error(f"Failed to create audiobook with chapters: {e}")
            raise
    
    async def get_all(
        self,
        skip: int = 0,
        limit: int = 20,
        status: Optional[AudioBookStatus] = None,
        search: Optional[str] = None
    ) -> List[AudioBook]:
        """获取所有有声读物"""
        try:
            stmt = select(AudioBook)
            
            # 状态过滤
            if status:
                stmt = stmt.where(AudioBook.status == status)
            
            # 搜索过滤
            if search:
                search_filter = or_(
                    AudioBook.title.ilike(f"%{search}%"),
                    AudioBook.author.ilike(f"%{search}%"),
                    AudioBook.description.ilike(f"%{search}%")
                )
                stmt = stmt.where(search_filter)
            
            # 排序和分页
            stmt = stmt.order_by(desc(AudioBook.created_at)).offset(skip).limit(limit)
            
            result = await self.db.fetch_all(stmt)
            return [item[0] for item in result] if result else []
            
        except Exception as e:
            logger.error(f"Failed to get all audiobooks: {e}")
            raise
    
    async def get_by_user(
        self,
        user_id: str,
        skip: int = 0,
        limit: int = 20,
        status: Optional[AudioBookStatus] = None,
        search: Optional[str] = None
    ) -> List[AudioBook]:
        """获取用户的有声读物"""
        try:
            stmt = select(AudioBook).where(AudioBook.user_id == user_id)
            
            # 状态过滤
            if status:
                stmt = stmt.where(AudioBook.status == status)
            
            # 搜索过滤
            if search:
                search_filter = or_(
                    AudioBook.title.ilike(f"%{search}%"),
                    AudioBook.author.ilike(f"%{search}%"),
                    AudioBook.description.ilike(f"%{search}%")
                )
                stmt = stmt.where(search_filter)
            
            # 排序和分页
            stmt = stmt.order_by(desc(AudioBook.created_at)).offset(skip).limit(limit)
            
            result = await self.db.fetch_all(stmt)
            return [item[0] for item in result] if result else []
            
        except Exception as e:
            logger.error(f"Failed to get audiobooks by user: {e}")
            raise
    
    async def get_public_audiobooks(
        self,
        skip: int = 0,
        limit: int = 20,
        language: Optional[str] = None,
        search: Optional[str] = None
    ) -> List[AudioBook]:
        """获取公开的有声读物"""
        try:
            stmt = select(AudioBook).where(AudioBook.status == AudioBookStatus.COMPLETED)
            
            # 语言过滤
            if language:
                stmt = stmt.where(AudioBook.language == language)
            
            # 搜索过滤
            if search:
                search_filter = or_(
                    AudioBook.title.ilike(f"%{search}%"),
                    AudioBook.author.ilike(f"%{search}%"),
                    AudioBook.description.ilike(f"%{search}%")
                )
                stmt = stmt.where(search_filter)
            
            # 排序和分页
            stmt = stmt.order_by(desc(AudioBook.created_at)).offset(skip).limit(limit)
            
            result = await self.db.fetch_all(stmt)
            return [item[0] for item in result] if result else []
            
        except Exception as e:
            logger.error(f"Failed to get public audiobooks: {e}")
            raise
    
    async def get_with_chapters(self, id: str, user_id: Optional[str] = None) -> Optional[AudioBook]:
        """获取有声读物及其章节"""
        try:
            stmt = select(AudioBook).where(AudioBook.id == id)
            
            # 用户权限检查
            if user_id:
                stmt = stmt.where(
                    or_(
                        AudioBook.user_id == user_id,
                        AudioBook.status == AudioBookStatus.COMPLETED
                    )
                )
            
            result = await self.db.fetch_one(stmt)
            
            if result:
                audiobook = result[0] if hasattr(result, '__getitem__') else result
                
                # 获取章节
                chapters_stmt = select(AudioBookChapter).where(
                    AudioBookChapter.audiobook_id == id
                ).order_by(AudioBookChapter.order)
                
                chapters_result = await self.db.fetch_all(chapters_stmt)
                audiobook.chapters = [item[0] for item in chapters_result] if chapters_result else []
                
                return audiobook
            
            return None
            
        except Exception as e:
            logger.error(f"Failed to get audiobook with chapters: {e}")
            raise
    
    async def update_status(self, id: str, status: AudioBookStatus, progress: float = None) -> bool:
        """更新有声读物状态"""
        try:
            audiobook = await self.get(id)
            if not audiobook:
                return False
            
            audiobook.status = status
            if progress is not None:
                audiobook.progress = progress
            
            # 设置完成时间
            if status == AudioBookStatus.COMPLETED:
                audiobook.completed_at = datetime.now(timezone.utc)
            
            await self.db.update(audiobook)
            
            logger.info(f"Updated audiobook {id} status to {status.value}")
            return True
            
        except Exception as e:
            logger.error(f"Failed to update audiobook status: {e}")
            raise
    
    async def get_statistics(self, user_id: str) -> Dict[str, Any]:
        """获取用户有声读物统计"""
        try:
            # 总数统计
            total_stmt = select(func.count(AudioBook.id)).where(AudioBook.user_id == user_id)
            total_count = await self.db.fetch_scalar(total_stmt)
            
            # 状态统计
            status_stats = {}
            for status in AudioBookStatus:
                status_stmt = select(func.count(AudioBook.id)).where(
                    and_(AudioBook.user_id == user_id, AudioBook.status == status)
                )
                status_count = await self.db.fetch_scalar(status_stmt)
                status_stats[status.value] = status_count or 0
            
            # 总时长统计
            duration_stmt = select(func.sum(AudioBook.total_duration)).where(
                and_(
                    AudioBook.user_id == user_id,
                    AudioBook.status == AudioBookStatus.COMPLETED
                )
            )
            total_duration = await self.db.fetch_scalar(duration_stmt)
            
            # 最近创建的
            recent_stmt = select(AudioBook).where(AudioBook.user_id == user_id).order_by(
                desc(AudioBook.created_at)
            ).limit(5)
            
            recent_result = await self.db.fetch_all(recent_stmt)
            recent_audiobooks = [item[0] for item in recent_result] if recent_result else []
            
            return {
                "total_count": total_count or 0,
                "status_stats": status_stats,
                "total_duration": total_duration or 0.0,
                "recent_audiobooks": [ab.to_dict() for ab in recent_audiobooks]
            }
            
        except Exception as e:
            logger.error(f"Failed to get audiobook statistics: {e}")
            raise


class AudioBookChapterRepository(BaseRepository[AudioBookChapter, Any, Any]):
    """有声读物章节Repository"""
    
    def __init__(self, session: AsyncSession = None):
        super().__init__(AudioBookChapter, session)
    
    async def get_by_audiobook(self, audiobook_id: str) -> List[AudioBookChapter]:
        """获取有声读物的所有章节"""
        try:
            stmt = select(AudioBookChapter).where(
                AudioBookChapter.audiobook_id == audiobook_id
            ).order_by(AudioBookChapter.order)
            
            result = await self.db.fetch_all(stmt)
            return [item[0] for item in result] if result else []
            
        except Exception as e:
            logger.error(f"Failed to get chapters by audiobook: {e}")
            raise
    
    async def get_next_chapter(self, audiobook_id: str, current_order: int) -> Optional[AudioBookChapter]:
        """获取下一章节"""
        try:
            stmt = select(AudioBookChapter).where(
                and_(
                    AudioBookChapter.audiobook_id == audiobook_id,
                    AudioBookChapter.order > current_order
                )
            ).order_by(AudioBookChapter.order).limit(1)
            
            result = await self.db.fetch_one(stmt)
            
            if result:
                return result[0] if hasattr(result, '__getitem__') else result
            return None
            
        except Exception as e:
            logger.error(f"Failed to get next chapter: {e}")
            raise
    
    async def update_status(self, id: str, status: AudioBookStatus, duration: float = None) -> bool:
        """更新章节状态"""
        try:
            chapter = await self.get(id)
            if not chapter:
                return False
            
            chapter.status = status
            if duration is not None:
                chapter.duration = duration
            
            # 设置完成时间
            if status == AudioBookStatus.COMPLETED:
                chapter.completed_at = datetime.now(timezone.utc)
            
            await self.db.update(chapter)
            
            logger.info(f"Updated chapter {id} status to {status.value}")
            return True
            
        except Exception as e:
            logger.error(f"Failed to update chapter status: {e}")
            raise


class AudioBookProgressRepository(CacheableRepository[AudioBookProgress, Any, Any]):
    """有声读物进度Repository"""
    
    def __init__(self, session: AsyncSession = None):
        super().__init__(AudioBookProgress, session, cache_ttl=1800)  # 30分钟缓存
    
    async def get_user_progress(self, user_id: str, audiobook_id: str) -> Optional[AudioBookProgress]:
        """获取用户阅读进度"""
        cache_key = f"user_progress:{user_id}:{audiobook_id}"
        
        # 尝试从缓存获取
        cached_progress = await self.get_cached(cache_key)
        if cached_progress:
            return cached_progress
        
        # 从数据库获取
        try:
            stmt = select(AudioBookProgress).where(
                and_(
                    AudioBookProgress.user_id == user_id,
                    AudioBookProgress.audiobook_id == audiobook_id
                )
            )
            
            result = await self.db.fetch_one(stmt)
            
            if result:
                progress = result[0] if hasattr(result, '__getitem__') else result
                # 缓存结果
                await self.cache_client.set(cache_key, progress.to_dict(), expire=self.cache_ttl)
                return progress
            
            return None
            
        except Exception as e:
            logger.error(f"Failed to get user progress: {e}")
            raise
    
    @transactional
    async def update_progress(
        self,
        user_id: str,
        audiobook_id: str,
        current_chapter: int,
        current_position: float,
        total_progress: float = None
    ) -> AudioBookProgress:
        """更新阅读进度"""
        try:
            # 获取现有进度
            progress = await self.get_user_progress(user_id, audiobook_id)
            
            if not progress:
                # 创建新进度记录
                progress_data = {
                    "user_id": user_id,
                    "audiobook_id": audiobook_id,
                    "current_chapter": current_chapter,
                    "current_position": current_position,
                    "total_progress": total_progress or 0.0,
                    "last_read_at": datetime.now(timezone.utc)
                }
                progress = AudioBookProgress(**progress_data)
                await self.db.insert(progress)
            else:
                # 更新现有进度
                progress.current_chapter = current_chapter
                progress.current_position = current_position
                
                if total_progress is not None:
                    progress.total_progress = total_progress
                
                progress.last_read_at = datetime.now(timezone.utc)
                await self.db.update(progress)
            
            # 使缓存失效
            cache_key = f"user_progress:{user_id}:{audiobook_id}"
            await self.invalidate_cache(cache_key)
            
            logger.info(f"Updated progress for user {user_id}, audiobook {audiobook_id}")
            return progress
            
        except Exception as e:
            logger.error(f"Failed to update progress: {e}")
            raise
    
    async def get_user_reading_history(self, user_id: str, limit: int = 10) -> List[AudioBookProgress]:
        """获取用户阅读历史"""
        try:
            stmt = select(AudioBookProgress).where(
                AudioBookProgress.user_id == user_id
            ).order_by(desc(AudioBookProgress.last_read_at)).limit(limit)
            
            result = await self.db.fetch_all(stmt)
            return [item[0] for item in result] if result else []
            
        except Exception as e:
            logger.error(f"Failed to get reading history: {e}")
            raise


class AudioBookReviewRepository(BaseRepository[AudioBookReview, Any, Any]):
    """有声读物评论Repository"""
    
    def __init__(self, session: AsyncSession = None):
        super().__init__(AudioBookReview, session)
    
    async def get_by_audiobook(
        self,
        audiobook_id: str,
        skip: int = 0,
        limit: int = 20,
        public_only: bool = True
    ) -> List[AudioBookReview]:
        """获取有声读物评论"""
        try:
            stmt = select(AudioBookReview).where(AudioBookReview.audiobook_id == audiobook_id)
            
            if public_only:
                stmt = stmt.where(AudioBookReview.is_public == True)
            
            stmt = stmt.order_by(desc(AudioBookReview.created_at)).offset(skip).limit(limit)
            
            result = await self.db.fetch_all(stmt)
            return [item[0] for item in result] if result else []
            
        except Exception as e:
            logger.error(f"Failed to get reviews by audiobook: {e}")
            raise
    
    async def get_average_rating(self, audiobook_id: str) -> Optional[float]:
        """获取平均评分"""
        try:
            stmt = select(func.avg(AudioBookReview.rating)).where(
                and_(
                    AudioBookReview.audiobook_id == audiobook_id,
                    AudioBookReview.is_public == True
                )
            )
            
            result = await self.db.fetch_scalar(stmt)
            return float(result) if result else None
            
        except Exception as e:
            logger.error(f"Failed to get average rating: {e}")
            raise
    
    async def update_helpful_count(self, review_id: str) -> bool:
        """更新有用数"""
        try:
            review = await self.get(review_id)
            if not review:
                return False
            
            review.helpful_count += 1
            await self.db.update(review)
            
            return True
            
        except Exception as e:
            logger.error(f"Failed to update helpful count: {e}")
            raise


class AudioBookTagRepository(BaseRepository[AudioBookTag, Any, Any]):
    """有声读物标签Repository"""
    
    def __init__(self, session: AsyncSession = None):
        super().__init__(AudioBookTag, session)
    
    async def get_by_user(self, user_id: str) -> List[str]:
        """获取用户的所有标签"""
        try:
            stmt = select(AudioBookTag.tag).where(AudioBookTag.user_id == user_id).distinct()
            
            result = await self.db.fetch_all(stmt)
            return [item[0] for item in result] if result else []
            
        except Exception as e:
            logger.error(f"Failed to get tags by user: {e}")
            raise
    
    async def get_by_audiobook(self, audiobook_id: str) -> List[AudioBookTag]:
        """获取有声读物标签"""
        try:
            stmt = select(AudioBookTag).where(AudioBookTag.audiobook_id == audiobook_id)
            
            result = await self.db.fetch_all(stmt)
            return [item[0] for item in result] if result else []
            
        except Exception as e:
            logger.error(f"Failed to get tags by audiobook: {e}")
            raise
    
    @transactional
    async def set_tags(self, user_id: str, audiobook_id: str, tags: List[str]) -> List[AudioBookTag]:
        """设置有声读物标签"""
        try:
            # 删除现有标签
            delete_stmt = delete(AudioBookTag).where(
                and_(
                    AudioBookTag.user_id == user_id,
                    AudioBookTag.audiobook_id == audiobook_id
                )
            )
            await self.db.execute(delete_stmt)
            
            # 添加新标签
            tag_objects = []
            for tag in tags:
                tag_data = {
                    "user_id": user_id,
                    "audiobook_id": audiobook_id,
                    "tag": tag
                }
                tag_obj = AudioBookTag(**tag_data)
                await self.db.insert(tag_obj)
                tag_objects.append(tag_obj)
            
            return tag_objects
            
        except Exception as e:
            logger.error(f"Failed to set tags: {e}")
            raise
